﻿namespace DataIntegration.Storage.SQL.SQLite
{
    using System;
    using System.Data;
    using System.Data.Common;
    using System.Data.SQLite;

    public class SQLiteDataStore : SQLDataStore
    {
        public SQLiteDataStore(string connectionString)
            : base(connectionString)
        {
        }

        public override DataTable Load(string tableName, string filter)
        {
            DataTable dataTable = new DataTable(tableName);

            using (SQLiteConnection connection = new SQLiteConnection(this.ConnectionString))
            {
                connection.Open();
                SQLiteCommand command = new SQLiteCommand(filter, connection);
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                adapter.Fill(dataTable);
            }

            return dataTable;
        }

        public override void Clear(string tableName)
        {
            using (SQLiteConnection connection = new SQLiteConnection(this.ConnectionString))
            {
                SQLiteCommand command = new SQLiteCommand(
                    string.Format("DELETE FROM '{0}'", tableName), 
                    connection);
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public override string Convert(object value)
        {
            Type type = value.GetType();
            string format;

            if (type.Equals(typeof(int)) || type.Equals(typeof(double)))
            {
                format = "{0}";
            }
            else if (type.Equals(typeof(DateTime)))
            {
                format = "'{0:dd MMM yyyy}'";
            }
            else
            {
                format = "'{0}'";
            }

            return string.Format(format, value);
        }

        public override DbConnection GetConnection()
        {
            return new SQLiteConnection(this.ConnectionString);
        }

        public override DbCommand BuildCommand(string commandText)
        {
            return new SQLiteCommand(commandText);
        }
    }
}